﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows;
using System.Windows.Controls;
using System.Threading;
using System.ComponentModel;
using System.IO;
using Microsoft.SqlServer.Server;


namespace bd2wpf
{
    public partial class MainWindow : Window
    {
            SqlConnection con;//Ссылка на сервер
            public string url;//Адресс сервера

            public void ListToBD() // Записываем в БД данные из записи
            {
                string F = "','";
                int n = dataSourceMyClass.myClasses.Count;
                for (int i = 0; i < n; i++)
                {
                    //Создаем sql запросы с помощью хранимых процедур
                    string s = "Contacts..SP_InsPersonality " + Convert.ToString(i + 100) + ",'" + dataSourceMyClass.myClasses[i].LastName + F + dataSourceMyClass.myClasses[i].Name + F 
                        + dataSourceMyClass.myClasses[i].Patronymic + F + dataSourceMyClass.myClasses[i].SexString + F + dataSourceMyClass.myClasses[i].Mobily + F 
                        + dataSourceMyClass.myClasses[i].Internal + F + dataSourceMyClass.myClasses[i].EMail + F + dataSourceMyClass.myClasses[i].Photo + F 
                        + dataSourceMyClass.myClasses[i].Info + "'," + Convert.ToString(i + 200) + "," + Convert.ToString(i + 300);
                    SqlCommand sc = new SqlCommand(s);
                    sc.Connection = con;
                    sc.ExecuteScalar();
                    string s2 = "Contacts..SP_InsPlaceResidence " + Convert.ToString(i + 200) + ",'" + dataSourceMyClass.myClasses[i].City + F + dataSourceMyClass.myClasses[i].Street + F
                        + dataSourceMyClass.myClasses[i].Home + F + dataSourceMyClass.myClasses[i].Flat + "'";
                    SqlCommand sc2 = new SqlCommand(s2);
                    sc2.Connection = con;
                    sc2.ExecuteScalar();
                    string s3 = "Contacts..SP_InsPosition " + Convert.ToString(i + 300) + ",'" + dataSourceMyClass.myClasses[i].Position + "'," + Convert.ToString(i + 400); 
                    SqlCommand sc3 = new SqlCommand(s3);
                    sc3.Connection = con; 
                    sc3.ExecuteScalar();
                    string s4 = "Contacts..SP_InsSubdivision " + Convert.ToString(i + 400) + ",'" + dataSourceMyClass.myClasses[i].Subdivision + "'";
                    SqlCommand sc4 = new SqlCommand(s4);
                    sc4.Connection = con;
                    sc4.ExecuteScalar();
                }
            }

            public void Connect_url(object sender, DoWorkEventArgs e)
            {
                con = new SqlConnection("Data Source=" + url + ";Integrated Security=True;Asynchronous Processing=true;MultipleActiveResultSets=true;");//Создаем адресс подключения с параметрами
                try
                {

                    con.Open();//Соединение с сервером
                    SqlCommand DB = new SqlCommand("SELECT name FROM sys.databases WHERE name = 'Contacts'");//Запрос проверки созданна ли таблица Contacts
                    DB.Connection = con;
                    var d1 = DB.ExecuteReader();
                    if (d1.Read() == false) //Проверяем не созданна ли таблица Contacts. Если нет, то создаем ее и заполняем хранимыми процедурами и вводим тестовые данные.
                    {
                        SqlCommand T = new SqlCommand("CREATE DATABASE Contacts;", con);
                        SqlCommand T2 = new SqlCommand("USE Contacts;", con);
                        SqlCommand CreatBD = new SqlCommand(bd2wpf.Properties.Resources.CREATE_DB, con);
                        SqlCommand Select = new SqlCommand(bd2wpf.Properties.Resources.SP_select_all_data, con);
                        SqlCommand Insert = new SqlCommand(bd2wpf.Properties.Resources.SP_insert, con);
                        SqlCommand Insert2 = new SqlCommand(bd2wpf.Properties.Resources.SP_insert2, con);
                        SqlCommand Insert3 = new SqlCommand(bd2wpf.Properties.Resources.SP_insert3, con);
                        SqlCommand Insert4 = new SqlCommand(bd2wpf.Properties.Resources.SP_insert4, con);
                        SqlCommand DeleteAll = new SqlCommand(bd2wpf.Properties.Resources.SP_delete_all_data, con);
                        SqlCommand Test = new SqlCommand(bd2wpf.Properties.Resources.Insert_test, con);
                        T.ExecuteNonQuery();
                        T2.ExecuteNonQuery();
                        CreatBD.ExecuteNonQuery();
                        Select.ExecuteNonQuery();
                        Insert.ExecuteNonQuery();
                        Insert2.ExecuteNonQuery();
                        Insert3.ExecuteNonQuery();
                        Insert4.ExecuteNonQuery();
                        DeleteAll.ExecuteNonQuery();
                        Test.ExecuteNonQuery();
                    }
                    SqlCommand cmd = new SqlCommand("Contacts..SP_Select_from_DB", con);//Хранимая процедура на выборку всех данных
                    cmd.Connection = con;
                    var data = cmd.ExecuteReader();//выборка данных из БД
                    while (data.Read())
                    {
                            #region Проверяем не являются ли данные в таблице null, и если да, то заменяем их на пустую строку. Если не делать, то при null выходит ошибка.

                            string Name, LastName, Patronymic, SexString, Mobily, Internal, EMail, Photo, City, Street, Home, Flat, Position, Subdivision, Info;
                            if (data.IsDBNull(0) == true) { LastName = ""; } else { LastName = data.GetString(0); }
                            if (data.IsDBNull(1) == true) { Name = ""; } else { Name = data.GetString(1); }
                            if (data.IsDBNull(2) == true) { Patronymic = ""; } else { Patronymic = data.GetString(2); }
                            if (data.IsDBNull(3) == true) { SexString = ""; } else { SexString = data.GetString(3); }
                            if (data.IsDBNull(4) == true) { Mobily = ""; } else { Mobily = data.GetString(4); }
                            if (data.IsDBNull(5) == true) { Internal = ""; } else { Internal = data.GetString(5); }
                            if (data.IsDBNull(6) == true) { EMail = ""; } else { EMail = data.GetString(6); }
                            if (data.IsDBNull(7) == true) { Photo = ""; } else { Photo = data.GetString(7); }
                            if (data.IsDBNull(8) == true) { City = ""; } else { City = data.GetString(8); }
                            if (data.IsDBNull(9) == true) { Street = ""; } else { Street = data.GetString(9); }
                            if (data.IsDBNull(10) == true) { Home = ""; } else { Home = data.GetString(10); }
                            if (data.IsDBNull(11) == true) { Flat = ""; } else { Flat = data.GetString(11); }
                            if (data.IsDBNull(12) == true) { Position = ""; } else { Position = data.GetString(12); }
                            if (data.IsDBNull(13) == true) { Subdivision = ""; } else { Subdivision = data.GetString(13); }
                            if (data.IsDBNull(14) == true) { Info = ""; } else { Info = data.GetString(14); }
                            #endregion
                            dataSourceMyClass.myClasses.Add(new MyClass(Name, LastName, Patronymic, SexString,
                             Mobily, Internal, EMail, Photo, City, Street, Home, Flat, Position, Subdivision, Info));//Создаем новую строку записи
                    }
                    data.Close();
                  }
                catch (SqlException ex)
               {
                   MessageBox.Show(ex.Message);
                }

            }
    }
}
